package cn.edu.sdjzu.xg.bysj.dao;


import cn.edu.sdjzu.xg.bysj.domain.Student;
import cn.edu.sdjzu.xg.bysj.domain.StudentClass;
import cn.edu.sdjzu.xg.bysj.exception.BysjException;
import cn.edu.sdjzu.xg.bysj.service.StudentClassService;
import cn.edu.sdjzu.xg.bysj.util.Condition;
import cn.edu.sdjzu.xg.bysj.util.JdbcHelper;
import cn.edu.sdjzu.xg.bysj.util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.LocalDate;
import java.util.Collection;
import java.util.HashSet;
import java.util.List;

public final class StudentDao {
    private static final StudentDao studentDao = new StudentDao();
    private static Collection<Student> students;

    private StudentDao() {
    }

    public static StudentDao getInstance() {
        return studentDao;
    }

    public Collection<Student> findAll() throws SQLException {
        return StudentDao.students;
    }

    /**
     * @param connection    数据库连接对象
     * @param conditionList 查询条件
     * @param pagination    分页对象
     * @return
     * @throws SQLException
     */
    public Collection<Student> findAll(Connection connection, List<Condition> conditionList, Pagination pagination) throws SQLException, BysjException {
        //创建Student类型集合类对象，HashSet不保证结果中元素的顺序
        Collection<Student> desiredStudents = new HashSet<Student>();
        //创建查询学生的主语句
        StringBuilder sql_select = new StringBuilder("SELECT * FROM student ");
        //如果有条件，则生成条件子句，并附加到主语句后面
        if (conditionList != null) {
            String whereClause = Condition.toWhereClause(conditionList);
            sql_select.append(whereClause);
        }
        //获取Student中的元组总数
        int totalNum = CommonDao.getInstance().count(connection, "Student");
        if (pagination != null) {
            String paginationClause = pagination.toLimitClause(totalNum);
            sql_select.append(paginationClause);
        }

        PreparedStatement pstmt_select_student =
                connection.prepareStatement(sql_select.toString());

        ResultSet resultSet_select = pstmt_select_student.executeQuery();


        while (resultSet_select.next()) {
            int id = resultSet_select.getInt("id");
            String name = resultSet_select.getString("name");
            String no = resultSet_select.getString("no");
            Boolean sex = resultSet_select.getBoolean("sex");
            LocalDate birthdate = resultSet_select.getDate("birthdate").toLocalDate();
            Integer studentClass_id = resultSet_select.getInt("studentClass_id");
            StudentClass studentClass = StudentClassService.getInstance().find(studentClass_id);
            Student desiredStudent = new Student(id, name, no, sex, birthdate, studentClass);
            desiredStudent.setId(id);
            desiredStudents.add(desiredStudent);
        }
        //关闭ResultSet, statement资源
        JdbcHelper.close(resultSet_select, pstmt_select_student);
        return desiredStudents;
    }

    public Student find(Connection connection, Integer id) throws SQLException, BysjException {
        //创建Student类型的引用，暂不创建对象
        Student desiredStudent = null;
        String sql_select_student_id = "SELECT * FROM Student WHERE id = ?";
        PreparedStatement pstmt_select_student_id =
                connection.prepareStatement(sql_select_student_id);
        pstmt_select_student_id.setInt(1, id);
        ResultSet resultSet_select_student_id = pstmt_select_student_id.executeQuery();
        //如果表中存在id对应的记录，则获得表中的字段值，并创建对象
        if (resultSet_select_student_id.next()) {
            String name = resultSet_select_student_id.getString("name");
            String no = resultSet_select_student_id.getString("no");
            Boolean sex = resultSet_select_student_id.getBoolean("sex");
            LocalDate birthdate = resultSet_select_student_id.getDate("birthdate").toLocalDate();
            Integer studentClassId = resultSet_select_student_id.getInt("student_class_id");
            StudentClass studentClass = StudentClassService.getInstance().find(studentClassId);
            desiredStudent = new Student(id, name, no, sex, birthdate, studentClass);
        }
        //关闭ResultSet, statement资源
        JdbcHelper.close(resultSet_select_student_id, pstmt_select_student_id);
        return desiredStudent;
    }

    public boolean update(Student student) throws SQLException {
        students.remove(student);
        return students.add(student);
    }

    /**
     * 由于Student扩展了Actor，前者对象id取后者的id的值，故参数student是有id的
     * 用于事务。调用本方法的Service，在调用本方法后，还会调用UserDao.add方法
     * 两次对Dao的调用要保证是同一个Connection对象，才能将它们设置在同一事务中
     *
     * @param student    要增加到数据库表中的Student对象
     * @param connection 来自Service的Connection对象
     * @throws SQLException
     */
    public void add(Student student, Connection connection) throws SQLException {
        //根据连接对象准备语句对象，如果SQL语句为多行，注意语句不同部分之间要有空格
        PreparedStatement preparedStatement =
                connection.prepareStatement(
                        "INSERT INTO student " +
                                " (id, name,no, studentClass_id)" +
                                " VALUES (?,?,?,?)");
        preparedStatement.setInt(1, student.getId());
        preparedStatement.setString(2, student.getName());
        preparedStatement.setString(3, student.getNo());
        preparedStatement.setInt(4, student.getStudentClass().getId());
        preparedStatement.executeUpdate();
        //本方法是事务的一部分，不能释放数据库连接
        JdbcHelper.close(preparedStatement);
    }

    public boolean delete(Integer id, Connection connection) throws SQLException, BysjException {
        Student student = this.find(connection,id);
        return this.delete(student);
    }

    public boolean delete(Student student) throws SQLException {
        return students.remove(student);
    }
}
